from django.core.management import BaseCommand
from django.db import connection

from search.documents import CourseDocument


class Command(BaseCommand):
    help = '同步课程数据到es'

    def handle(self, *args, **options):
        #删除现有索引
        if CourseDocument._index.exists():
            CourseDocument._index.delete()
        # 确保索引存在
        CourseDocument.init()

        # SQL查询
        with connection.cursor() as cursor:
            cursor.execute("""
             SELECT
                        -- 课程基础字段
                        c.id AS course_id,
                        c.name AS course_name,
                        c.course_img,
                        c.course_type,
                        c.price,
                        c.original_price,
                        c.brief,
                        c.level AS course_level,
                        c.pub_date,
                        c.status,
                        c.students,
                        c.lessons,
                        c.pub_lessons,
                        c.teacher_id,
                        c.audience,
                        c.description AS course_description,
                        c.outcomes,
                        c.created_time,
                        c.updated_time,
                        c.is_deleted,
                        c.is_show,
                        c.orders AS course_orders,
												teacher.`name` AS teacher_name,
												teacher.title AS teacher_title,

                        -- 关联分类信息
                        GROUP_CONCAT(
                            DISTINCT cat.id
                            ORDER BY cat.level ASC
                            SEPARATOR ' | '
                        ) AS category_ids,
                        GROUP_CONCAT(
                            DISTINCT cat.name
                            ORDER BY cat.level ASC
                            SEPARATOR ' | '
                        ) AS category_names,
                        -- 关联导航信息
                        GROUP_CONCAT(
                            DISTINCT nav.id
                            ORDER BY nav.weight DESC
                            SEPARATOR ' | '
                        ) AS nav_ids,
                        GROUP_CONCAT(
                            DISTINCT nav.name
                            ORDER BY nav.weight DESC
                            SEPARATOR ' | '
                        ) AS nav_names,
                        GROUP_CONCAT(
                            DISTINCT nav.display_name
                            ORDER BY nav.weight DESC
                            SEPARATOR ' | '
                        ) AS nav_display_names
                    FROM tb_course c
                    -- 关联课程分类（多对多）
                    LEFT JOIN tb_course_categories cc ON c.id = cc.course_id
                    LEFT JOIN tb_course_category cat ON cc.coursecategory_id = cat.id
                    -- 关联导航分类（多对多）
                    LEFT JOIN tb_course_nav_relation cnr ON c.id = cnr.course_id
                    LEFT JOIN tb_nav_category nav ON cnr.nav_category_id = nav.id
										LEFT JOIN tb_teacher teacher ON c.teacher_id=teacher.id
                    GROUP BY c.id
                    ORDER BY c.orders ASC;
            """)

            rows = cursor.fetchall()
            # 获取列名
            columns = [col[0] for col in cursor.description]

            bulk_data = []

            # 批量处理数据
            for row in rows:
                # 将行数据转换为字典
                data = dict(zip(columns, row))

                # 创建文档实例
                doc = CourseDocument(
                    meta={'id': data['course_id']},
                    course_id=data['course_id'],
                    course_name=data['course_name'],
                    course_img=data['course_img'],
                    course_type=data['course_type'],
                    price=float(data['price']) if data['price'] is not None else 0.0,
                    original_price=float(data['original_price']) if data['original_price'] is not None else 0.0,
                    brief=data['brief'],
                    course_level=data['course_level'],
                    pib_date=data['pub_date'],
                    status=data['status'],
                    students=data['students'],
                    lessons=data['lessons'],
                    pub_lessons=data['pub_lessons'],
                    teacher_id=data['teacher_id'],
                    audience=data['audience'],
                    course_description=data['course_description'],
                    outcomes=data['outcomes'],
                    created_time=data['created_time'],
                    updated_time=data['updated_time'],
                    is_deleted=data['is_deleted'],
                    is_show=data['is_show'],
                    course_orders=data['course_orders'],
                    teacher_name=data['teacher_name'],
                    teacher_title=data['teacher_title'],
                    category_ids=data['category_ids'],
                    category_names=data['category_names'],
                    nav_ids=data['nav_ids'],
                    nav_names=data['nav_names'],
                    nav_display_names=data['nav_display_names'],

                )

                bulk_data.append(doc)

            if bulk_data:
                # 批量写入
                CourseDocument.bulk(bulk_data)

        self.stdout.write(self.style.SUCCESS(f'同步课程数据到es成功,共{len(rows)}条'))
